﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CocisDBUtility;
using System.Data;
using System.Data.SqlClient;
using CocisModel;
using CocisModel.Common;

namespace CocisDAL
{
    public class AssetDAL
    {
        #region 新增PhysicalMachine物理机器信息
        public int AddPhysicalMachine(Asset.PhysicalMachineInfoDataTable dt)
        {
            Asset.PhysicalMachineInfoRow row = (Asset.PhysicalMachineInfoRow)dt.Rows[0];
            string sql = "INSERT INTO PhysicalMachineInfo(ServerTypeID,AssetNo,UniqueNo,Brand,Model,SiteCodeID,Floors,RackID,PositionInRack,iLOIP,CPU,RAM,HardDisk " +
                ",StatusID,EnclosureID,PduID,KvmID,SN,WarrantyStartDate,WarrantyEndDate,VendorID,VirtualCenter,VMVersion,VMClusterName,BakServerName,Comments) " +
                " VALUES(@ServerTypeID,@AssetNo,@UniqueNo,@Brand,@Model,@SiteCodeID,@Floors,@RackID,@PositionInRack,@iLOIP,@CPU,@RAM,@HardDisk " +
                ",@StatusID,@EnclosureID,@PduID,@KvmID,@SN,@WarrantyStartDate,@WarrantyEndDate,@VendorID,@VirtualCenter,@VMVersion,@VMClusterName,@BakServerName,@Comments)";
            SqlParameter[] param = {
                                       new SqlParameter("@ServerTypeID",row.ServerTypeID),
                                       new SqlParameter("@AssetNo",row.AssetNo),
                                       new SqlParameter("@UniqueNo",row.UniqueNo),
                                       new SqlParameter("@Brand",row.Brand),
                                       new SqlParameter("@Model",row.Model),
                                       new SqlParameter("@SiteCodeID",row.SiteCodeID),
                                       new SqlParameter("@Floors",row.Floors),
                                       new SqlParameter("@RackId",row.RackID),
                                       new SqlParameter("@PositionInRack",row.PositionInRack),
                                       new SqlParameter("@iLOIP",row.iLOIP),
                                       new SqlParameter("@CPU",row.CPU),
                                       new SqlParameter("@RAM",row.RAM),
                                       new SqlParameter("@HardDisk",row.HardDisk),
                                       new SqlParameter("@StatusID",row.StatusID),
                                       new SqlParameter("@EnclosureID",row.EnclosureID),
                                       new SqlParameter("@PduID",row.PduID),
                                       new SqlParameter("@KvmID",row.KvmID), 
                                       new SqlParameter("@SN",row.SN),
                                       new SqlParameter("@WarrantyStartDate",row.WarrantyStartDate),
                                       new SqlParameter("@WarrantyEndDate",row.WarrantyEndDate),
                                       new SqlParameter("@VendorId",row.VendorID),
                                       new SqlParameter("@VirtualCenter",row.VirtualCenter),  
                                       new SqlParameter("@VMVersion",row.VMVersion), 
                                       new SqlParameter("@VMClusterName",row.VMClusterName), 
                                       new SqlParameter("@BakServerName",row.BakServerName),                 
                                       new SqlParameter("@Comments",row.Comments)
                                   };

            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql,param); 
        }
        #endregion

        #region 查询PhysicalMachine信息
        public DataSet GetPhysicalMachine(int ServerTypeID, string AssetNo, string UniqueNo, string SN, int statusID, string SiteCodeID, string VMClusterName, string BakServerName)
        {

            string sql = " select * from viewPhysicalMachine where 1=1 ";
            if (AssetNo != "")
            {
                sql += " and assetNo like '%" + AssetNo + "%' ";
            }
            if (SN != "")
            {
                sql += " and SN like '%" + SN + "%' ";
            }
            if (statusID != 0)
            {
                sql += " and statusID= '" + statusID + "' ";
            }
            
        
            if (SiteCodeID != "")
            {
                sql += " and siteCodeID in( " + SiteCodeID + " ) ";
            }

            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);

        }
        #endregion

        #region 新增Server信息
        public int AddServer(Asset.tbServerDataTable dt)
        {
            Asset.tbServerRow row = (Asset.tbServerRow)dt.Rows[0];
            string sql = "INSERT INTO tbServer(PhysicalID,ServerName,Alias,ServerIP,DomainID,OSNameID,OSVersion,OSServicePackID,EnvironmentID,CPU,RAM,HardDisk " +
                ",StatusID,ScomStatusID,PatchStatusID,CVCoverageID,AppCatalog,HostingTypeID,AppSupportTeam,BuID,BUdetails,IfCluster,ServerTypeID,ClusterID) " +
                " VALUES(@PhysicalID,@ServerName,@Alias,@ServerIP,@DomainID,@OSNameID,@OSVersion,@OSServicePackID,@EnvironmentID,@CPU,@RAM,@HardDisk " +
                ",@StatusID,@ScomStatusID,@PatchStatusID,@CVCoverageID,@AppCatalog,@HostingTypeID,@AppSupportTeam,@BuID,@BUdetails,@IfCluster,@ServerTypeID,@ClusterID)";

            SqlParameter[] param = {
                                       new SqlParameter("@PhysicalID",row.PhysicalID),
                                       new SqlParameter("@ServerName",row.ServerName),       
                                       new SqlParameter("@Alias",row.Alias),
                                       new SqlParameter("@ServerIP",row.ServerIP),
                                       new SqlParameter("@DomainID",row.DomainID),
                                       new SqlParameter("@OSNameID",row.OSNameID),
                                       new SqlParameter("@OSVersion",row.OSVersion),
                                       new SqlParameter("@OSServicePackID",row.OSServicePackID),
                                       new SqlParameter("@EnvironmentID",row.EnvironmentID),
                                       new SqlParameter("@CPU",row.CPU),
                                       new SqlParameter("@RAM",row.RAM),
                                       new SqlParameter("@HardDisk",row.HardDisk),
                                       new SqlParameter("@StatusID",row.StatusID),
                                       new SqlParameter("@ScomStatusID",row.ScomStatusID),
                                       new SqlParameter("@PatchStatusID",row.PatchStatusID),
                                       new SqlParameter("@CVCoverageID",row.CVCoverageID),
                                       new SqlParameter("@AppCatalog",row.AppCatalog),
                                       new SqlParameter("@HostingTypeID",row.HostingTypeID),
                                       new SqlParameter("@AppSupportTeam",row.AppSupportTeam),
                                       new SqlParameter("@BuID",row.BuID),
                                       new SqlParameter("@BUdetails",row.BUdetails),
                                       new SqlParameter("@IfCluster",row.IfCluster),
                                       new SqlParameter("@ServerTypeID",row.ServerTypeID),
                                       new SqlParameter("@ClusterID",row.ClusterID)
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询Server信息
        public DataSet GetServerColumn(int type)
        {
            string sql = "";
            if (type == 1)//系统节点
            {
                sql = " select top 1 * from ViewServer";

            }
            if (type == 2)//物理服务器
            {


            }
            if (type == 3)//虚拟服务器
            {

            }
            if (type == 4)//集群服务器
            {


            }
            if (type == 5)//数据库实例
            {


            }
            if (type == 6)//数据库具体
            {

            }


            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);

        }

        public DataSet GetServer(string ServerName, string Alias, string ServerIP, int OSNameID, string OSVersion, int OSServicePackID, int EnvironmentID)
        {
            //string ServerName, string Alias, string ServerIP, int OSNameID, string OSVersion, string OSServicePackID, int EnvironmentID, string CPU, string RAM, string HardDisk, int StatusID, int ScomStatusID, int PatchStatusID, int CVCoverageID, string AppCatalog, int HostingTypeID, string AppSupportTeam, int BuID, string BUdetails, string IfCluster, int ServerTypeID

            string sql = " select * from viewServer where 1=1 ";
            if (ServerName != "")
            {
                sql += " and ServerName like '%" + ServerName + "%' ";
            }

            if (Alias != "")
            {
                sql += " and Alias like '%" + Alias + "%' ";
            }

            if (ServerIP != "")
            {
                sql += " and ServerIP like '%" + ServerIP + "%' ";
            }

            if (OSNameID != 0)
            {
                sql += " and OSNameID= '" + OSNameID + "' ";
            }

            if (OSVersion != "")
            {
                sql += " and OSVersion like '%" + OSVersion + "%' ";
            }

            if (OSServicePackID != 0)
            {
                sql += " and OSServicePackID= '" + OSServicePackID + "' ";
            }

            if (EnvironmentID != 0)
            {
                sql += " and EnvironmentID in( " + EnvironmentID + " ) ";
            }

            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增ClusterServer信息
        public int AddClusterServer(Asset.ClusterServerInfoDataTable dt)
        {
            Asset.ClusterServerInfoRow row = (Asset.ClusterServerInfoRow)dt.Rows[0];
            string sql = "INSERT INTO ClusterServerInfo(ClusterType,ClusterName,ClusterIP,ResourceGroup,NLBModeID) " +
                " values(@ClusterType,@ClusterName,@ClusterIP,@ResourceGroup,@NLBModeID)";

            SqlParameter[] param = {
                                       new SqlParameter("@ClusterType",row.ClusterType),      
                                       new SqlParameter("@ClusterName",row.ClusterName),
                                       new SqlParameter("@ClusterIP",row.ClusterIP),
                                       new SqlParameter("@ResourceGroup",row.ResourceGroup),
                                       new SqlParameter("@NLBModeID",row.NLBModeID)
                                   };

            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询ClusterServer信息
        public DataSet GetClusterServer()
        {
            string sql = "select * from ClusterServerInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询Domain信息
        public DataSet GetDomain()
        {
            string sql = "select * from DomainInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询OSName信息
        public DataSet GetOSName()
        {
            string sql = "select * from OSNameInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询OSServicePack信息
        public DataSet GetOSServicePack()
        {
            string sql = "select * from OSServicePackInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询Environment信息
        public DataSet GetEnvironment()
        {
            string sql = "select * from EnvironmentInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询Status信息
        public DataSet GetStatus()
        {
            string sql = "select * from StatusInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询ScomStatus信息
        public DataSet GetScomStatus()
        {
            string sql = "select * from ScomStatusInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询PatchStatus信息
        public DataSet GetPatchStatus()
        {
            string sql = "select * from PatchStatusInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询CVCoverage信息
        public DataSet GetCVCoverage()
        {
            string sql = "select * from CVCoverageInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询HostingType信息
        public DataSet GetHostingType()
        {
            string sql = "select * from HostingTypeInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询BU信息
        public DataSet GetBU()
        {
            string sql = "select * from BuInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询ServerType信息
        public DataSet GetServerType()
        {
            string sql = "select * from ServerTypeInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询SiteCode信息
        public DataSet GetSiteCode()
        {
            string sql = "select * from SiteCodeInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询Rack信息
        public DataSet GetRack()
        {
            string sql = "select * from RackInfo";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增Enclosure信息
        public int AddEnclosure(Asset.tbEnclosureDataTable dt)
        {
            Asset.tbEnclosureRow row = (Asset.tbEnclosureRow)dt.Rows[0];
            string sql = "INSERT INTO tbEnclosure(AssetNo, EnclosureName, StatusID, Brand, SiteCodeID, Floors, SN, UUID, PartNumber, WarrantyStartDate, WarrantyEndDate,VendorID) " +
                " VALUES(@AssetNo, @EnclosureName, @StatusID, @Brand, @SiteCodeID, @Floors, @SN, @UUID, @PartNumber, @WarrantyStartDate, @WarrantyEndDate,@VendorID)";

            SqlParameter[] param = {
                                       new SqlParameter("@AssetNo",row.AssetNo),
                                       new SqlParameter("@EnclosureName",row.EnclosureName),       
                                       new SqlParameter("@StatusID",row.StatusID),
                                       new SqlParameter("@Brand",row.Brand),
                                       new SqlParameter("@SiteCodeID",row.SiteCodeID),
                                       new SqlParameter("@Floors",row.Floors),
                                       new SqlParameter("@SN",row.SN),       
                                       new SqlParameter("@UUID",row.UUID),
                                       new SqlParameter("@PartNumber",row.PartNumber),
                                       new SqlParameter("@WarrantyStartDate",row.WarrantyStartDate),
                                       new SqlParameter("@WarrantyEndDate",row.WarrantyEndDate),
                                       new SqlParameter("@VendorID",row.VendorID)     
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询Enclosure信息
        public DataSet GetEnclosure()
        {
            string sql = "select * from tbEnclosure ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询PDU信息
        public DataSet GetPDU()
        {
            string sql = "select * from PduInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增KVM信息
        public int AddKVM(Asset.KvmInfoDataTable dt)
        {
            Asset.KvmInfoRow row = (Asset.KvmInfoRow)dt.Rows[0];
            string sql = "INSERT INTO KvmInfo(AssetNo, KvmName, KvmPort, Brand, Model, SiteCodeID, Floors, RackID, SN, WarrantyStartDate, WarrantyEndDate,VendorID) " +
                " VALUES(@AssetNo, @KvmName, @KvmPort, @Brand, @Model, @SiteCodeID, @Floors, @RackID, @SN, @WarrantyStartDate, @WarrantyEndDate,@VendorID)";

            SqlParameter[] param = {
                                       new SqlParameter("@AssetNo",row.AssetNo),
                                       new SqlParameter("@KvmName",row.KvmName),       
                                       new SqlParameter("@KvmPort",row.KvmPort),
                                       new SqlParameter("@Brand",row.Brand),
                                       new SqlParameter("@Model",row.Model),
                                       new SqlParameter("@SiteCodeID",row.SiteCodeID),
                                       new SqlParameter("@Floors",row.Floors),
                                       new SqlParameter("@RackID",row.RackID),
                                       new SqlParameter("@SN",row.SN),       
                                       new SqlParameter("@WarrantyStartDate",row.WarrantyStartDate),
                                       new SqlParameter("@WarrantyEndDate",row.WarrantyEndDate),
                                       new SqlParameter("@VendorID",row.VendorID)     
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询KVM信息
        public DataSet GetKVM()
        {
            string sql = "select * from KvmInfo";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询Vendor信息
        public DataSet GetVendor()
        {
            string sql = "select * from VendorInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询NLBMode信息
        public DataSet GetNLBMode()
        {
            string sql = "select * from NLBModeInfo where 1=1 ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增IPRange信息
        public int AddIPRange(Asset.IPRangeDataTable dt)
        {
            Asset.IPRangeRow row = (Asset.IPRangeRow)dt.Rows[0];
            string sql = "INSERT INTO tbIPRange(IPRangeID,IPRange,StartIP,EndIP,IPType) " +
                " VALUES(@IPRangeID,@IPRange,@StartIP,@EndIP,@IPType)";

            SqlParameter[] param = {
                                       new SqlParameter("@IPRangeID",row.IPRangeID),
                                       new SqlParameter("@IPRange",row.IPRange),       
                                       new SqlParameter("@StartIP",row.StartIP),
                                       new SqlParameter("@EndIP",row.EndIP),
                                       new SqlParameter("@IPType",row.IPType)
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询IPRange信息
        public DataSet GetIPRange()
        {
            string sql = "select * from tbIPRange where 1=1 ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增IPAssign信息
        public int AddIPAssign(Asset.IPAssignDataTable dt)
        {
            Asset.IPAssignRow row = (Asset.IPAssignRow)dt.Rows[0];
            string sql = "INSERT INTO tbIPAssign(IPRangeID,IPAddress, Netmask, Gateway, VlanID, Usage, DeviceName, CRQ) " +
                " VALUES(@IPRangeID,@IPAddress,@Netmask,@Gateway,@VlanID,@Usage,@DeviceName,@CRQ)";

            SqlParameter[] param = {
                                       new SqlParameter("@IPRangeID",row.IPRangeID),
                                       new SqlParameter("@IPAddress",row.IPAddress),       
                                       new SqlParameter("@Netmask",row.Netmask),
                                       new SqlParameter("@Gateway",row.Gateway),
                                       new SqlParameter("@VlanID",row.VlanID),
                                       new SqlParameter("@Usage",row.Usage),
                                       new SqlParameter("@DeviceName",row.DeviceName),
                                       new SqlParameter("@CRQ",row.CRQ),
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询IPAssign信息
        public DataSet GetIPAssign()
        {
            string sql = "select * from tbIPAssign where 1=1 ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增Network信息
        public int AddNetwork(Asset.tbNetworkDataTable dt)
        {
            Asset.tbNetworkRow row = (Asset.tbNetworkRow)dt.Rows[0];
            string sql = "INSERT INTO tbNetwork(TypeID, AssetNo, UniqueNo, Brand, Model, SiteCodeID, Floors, RackID, PositionInRack, ManageIP, Capacity100Mbps, Capacity1Gbps, Capacity10Gbps, UplinkInfo, HostingTypeID, StatusID, PduID, SN, WarrantyStartDate, WarrantyEndDate, VendorID) " +
                " VALUES(@TypeID, @AssetNo, @UniqueNo, @Brand, @Model, @SiteCodeID, @Floors, @RackID, @PositionInRack, @ManageIP, @Capacity100Mbps, @Capacity1Gbps, @Capacity10Gbps, @UplinkInfo, @HostingTypeID, @StatusID, @PduID, @SN, @WarrantyStartDate, @WarrantyEndDate, @VendorID)";

            SqlParameter[] param = {
                                       new SqlParameter("@TypeID",row.TypeID),
                                       new SqlParameter("@AssetNo",row.AssetNo),       
                                       new SqlParameter("@UniqueNo",row.UniqueNo),
                                       new SqlParameter("@Brand",row.Brand),
                                       new SqlParameter("@Model",row.Model),
                                       new SqlParameter("@SiteCodeID",row.SiteCodeID),
                                       new SqlParameter("@Floors",row.Floors),
                                       new SqlParameter("@RackID",row.RackID),
                                       new SqlParameter("@PositionInRack",row.PositionInRack),
                                       new SqlParameter("@ManageIP",row.ManageIP),       
                                       new SqlParameter("@Capacity100Mbps",row.Capacity100Mbps),
                                       new SqlParameter("@Capacity1Gbps",row.Capacity1Gbps),
                                       new SqlParameter("@Capacity10Gbps",row.Capacity10Gbps),
                                       new SqlParameter("@UplinkInfo",row.UplinkInfo),
                                       new SqlParameter("@HostingTypeID",row.HostingTypeID),
                                       new SqlParameter("@StatusID",row.StatusID),
                                       new SqlParameter("@PduID",row.PduID),
                                       new SqlParameter("@SN",row.SN),       
                                       new SqlParameter("@WarrantyStartDate",row.WarrantyStartDate),
                                       new SqlParameter("@WarrantyEndDate",row.WarrantyEndDate),
                                       new SqlParameter("@VendorID",row.VendorID)
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询Network信息
        public DataSet GetNetwork()
        {
            string sql = "select * from tbNetwork where 1=1 ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询DBType信息
        public DataSet GetDBType()
        {
            string sql = "select * from DBTypeInfo ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增DBInstance数据库实例信息
        public int AddDBInstance(Asset.DBInstanceInfoDataTable dt)
        {
            Asset.DBInstanceInfoRow row = (Asset.DBInstanceInfoRow)dt.Rows[0];
            string sql = " INSERT INTO DBInstanceInfo(ServerID,FullInstanceName,DBTypeID,DBVersion,DBServicePack,AuthenticationMode,StatusID,SQLNetworkName,SQLNetworkIP，ClusterID)" +
            "  VALUES(@ServerID,@FullInstanceName,@DBTypeID,@DBVersion,@DBServicePack,@AuthenticationMode,@StatusID,@SQLNetworkName,@SQLNetworkIP，@ClusterID)";
            SqlParameter[] param = {

                                               new SqlParameter("@ServerID",row.ServerID),
                                               new SqlParameter("@FullInstanceName",row.FullInstanceName),
                                               new SqlParameter("@DBTypeID",row.DBTypeID),
                                               new SqlParameter("@DBVersion",row.DBVersion),
                                               new SqlParameter("@DBServicePack",row.DBServicePack),
                                               new SqlParameter("@AuthenticationMode",row.AuthenticationMode),                                      
                                               new SqlParameter("@StatusID",row.StatusID),
                                               new SqlParameter("@SQLNetworkName",row.SQLNetworkName),
                                               new SqlParameter("@SQLNetworkIP",row.SQLNetworkIP),
                                               new SqlParameter("@ClusterID",row.ClusterID)
                                            };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询DBInstance数据库实例信息
        public DataSet GetDBInstance(string FullInstanceName, string serverName, int dbTypeID, int statusID, string siteCodeInfoID)
        {
            string sql = "select ServerName,ServerIP,FullInstanceName,DBType,DBVersion,DBServicePack,AuthenticationMode,Status,SQLNetworkName,SQLNetworkIP,ClusterName  from viewDBInstance where 1=1 ";

            if (FullInstanceName != "")
            {
                sql += " and FullInstanceName like '%" + FullInstanceName + "%' ";
            }

            if (serverName != "")
            {
                sql += " and serverName like '%" + serverName + "%' ";
            }

            if (dbTypeID != 0)
            {
                sql += " and dbtypeID= '" + dbTypeID + "' ";
            }
            if (statusID != 0)
            {
                sql += " and statusID= '" + statusID + "' ";
            }

            //if (siteCodeInfoID != "")
            //{
            //    sql += " and PSiteCodeID in( " + siteCodeInfoID + " ) and VSiteCodeID in( " + siteCodeInfoID + ") ";
            //}
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增DatabaseInfo数据库信息
        public int AddDatabaseInfo(Asset.DatabaseInfoDataTable dt)
        {
            Asset.DatabaseInfoRow row = (Asset.DatabaseInfoRow)dt.Rows[0];
            string sql = " INSERT INTO DataBaseInfo(DBInstanceID,DBName,DBSize,UnallocatedSpace,LogSize,LogSpaceUsedPercent,DataAutogrowth,LogAutogrowth,StatusID" +
                " ,RecoveryModel,DBOwner,DBCreated,CVBackupID,[Application],DBSupportTeam)" +
            "  VALUES(@DBInstanceID,@DBName,@DBSize,@UnallocatedSpace,@LogSize,@LogSpaceUsedPercent,@DataAutogrowth,@LogAutogrowth,@StatusID,@RecoveryModel,@DBOwner,@DBCreated,@CVBackupID,@Applications,@DBSupportTeam)";
            SqlParameter[] param = {

                                               new SqlParameter("@DBInstanceID",row.DBInstanceID),
                                               new SqlParameter("@DBName",row.DBName),
                                               new SqlParameter("@DBSize",row.DBSize),
                                               new SqlParameter("@UnallocatedSpace",row.UnallocatedSpace),
                                               new SqlParameter("@LogSize",row.LogSize),
                                               new SqlParameter("@LogSpaceUsedPercent",row.LogSpaceUsedPercent),                                      
                                               new SqlParameter("@DataAutogrowth",row.DataAutogrowth),
                                               new SqlParameter("@LogAutogrowth",row.LogAutogrowth),
                                               new SqlParameter("@StatusID",row.StatusID),
                                               new SqlParameter("@RecoveryModel",row.RecoveryModel),
                                               new SqlParameter("@DBOwner",row.DBOwner),
                                               new SqlParameter("@DBCreated",row.DBCreated),
                                               new SqlParameter("@CVCoverageID",row.CVCoverageID),
                                               new SqlParameter("@Application",row.Application),
                                               new SqlParameter("@DBSupportTeam",row.DBSupportTeam)
                                            };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询DatabaseInfo数据库信息
        public DataSet GetDatabaseInfo()
        {
            string sql = @"FullInstanceName, DBName, DBSize, UnallocatedSpace, LogSize, LogSpaceUsedPercent, DataAutogrowth, LogAutogrowth, Status, RecoveryModel, DBOwner, DBCreated, CVCoverage, Application, DBSupportTeam
         from viewDatabase where 1=1 ";

            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 查询License信息
        public DataSet GetLicense()
        {
            string sql = "select * from LicenseInfo where 1=1 ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 新增GeneralSoftware信息
        public int AddGeneralSoftware(Asset.GeneralSoftwareInfoDataTable dt)
        {
            Asset.GeneralSoftwareInfoRow row = (Asset.GeneralSoftwareInfoRow)dt.Rows[0];
            string sql = "INSERT INTO GeneralSoftwareInfo(SiteCodeID,SoftwareName,SoftwareVersion,LicenseID,StatusID,WarrantyStartDate,WarrantyEndDate,VendorID)" +
                 " VALUES(@SiteCodeID,@SoftwareName,@SoftwareVersion,@LicenseID,@StatusID,@WarrantyStartDate,@WarrantyEndDate,@VendorID)";
            SqlParameter[] param = {
                                        new SqlParameter("@SiteCodeID",row.SiteCodeID),
                                        new SqlParameter("@SoftwareName",row.SoftwareName),
                                        new SqlParameter("@SoftwareVersion",row.SoftwareVersion),
                                        new SqlParameter("@LicenseID",row.LicenseID),
                                        new SqlParameter("@StatusID",row.StatusID),
                                        new SqlParameter("@WarrantyStartDate",row.WarrantyStartDate),
                                        new SqlParameter("@WarrantyEndDate",row.WarrantyEndDate),
                                        new SqlParameter("@VendorID",row.VendorID)
                                    };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);
        }
        #endregion

        #region 查询GeneralSoftware信息
        public DataSet GetGeneralSoftware()
        {
            string sql = "select * from GeneralSoftwareInfo where 1=1 ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

        #region 过滤Physical的DataSet
        public DataSet GetPhysicalServers(int serverTypeID,int serverStatusID, string iLOIP, string brand, DateTime warrantyStartDate, DateTime warrantyEndDate)
        {
            string sql = "select * from PhysicalMachineInfo where StatusID=@StatusID and ServerTypeID=@ServerTypeID and WarrantyStartDate>=@WarrantyStartDate and WarrantyEndDate<=@WarrantyEndDate ";
            List<SqlParameter> allParams = new List<SqlParameter>();
            allParams.Add(new SqlParameter("@StatusID", SqlDbType.Int, 4) { Value = serverStatusID });
            allParams.Add(new SqlParameter("@ServerTypeID", SqlDbType.Int, 4) { Value = serverTypeID });
            allParams.Add(new SqlParameter("@WarrantyStartDate", SqlDbType.Date) { Value = warrantyStartDate });
            allParams.Add(new SqlParameter("@WarrantyEndDate", SqlDbType.Date) { Value = warrantyEndDate });
            if (!String.IsNullOrEmpty(iLOIP))
            {
                sql += " and iLOIP like @iLOIP ";
                allParams.Add(new SqlParameter("@iLOIP", "%" + iLOIP + "%"));
            }
            if (!string.IsNullOrEmpty(brand))
            {
                sql += " and Brand like @Brand ";
                allParams.Add(new SqlParameter("@Brand", "%" + brand + "%"));
            }
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, allParams.ToArray());
        }
        #endregion

        public object GetServer()
        {
            throw new NotImplementedException();
        }

        public object GetDBInstance()
        {
            throw new NotImplementedException();
        }

        public object GetPhysicalMachine()
        {
            throw new NotImplementedException();
        }


        public DataSet GetServer(string serverName, string serverIP, int environmentId, string physical_VM, int statusID, int hostingTypeID, int BUID, string siteCodeInfo)
        {
            throw new NotImplementedException();
        }
    }
}
